﻿using DemoQLKhoSX.Library;
using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Text;
using DemoQLKhoSX.EntityClasses;

namespace DemoQLKhoSX.DatabaseAccessLayer
{
    public class HangDAO
    {
        #region "Variable"
        private SqlConnection _conn;
        private string strConn = @"workstation id=basetestweb.mssql.somee.com;packet size=4096;user id=baseadmin;pwd=dinhson@21;data source=basetestweb.mssql.somee.com;persist security info=False;initial catalog=basetestweb";
        private SqlDataAdapter da;
        private DBConnection conn;
        #endregion

        public HangDAO()
        {
            conn = new DBConnection();
        }

        public DataTable searchByID(string id)
        {
            string query = string.Format("select * from TBL_Hang where IDHang = @idhang or @idhang = ''");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@idhang", SqlDbType.VarChar);
            sqlParameters[0].Value = Convert.ToString(id);
            return conn.executeSelectQuery(query, sqlParameters);
        }

        public void OpenConnect()
        {
            if (_conn == null)
                _conn = new SqlConnection(strConn);
            if (_conn.State == ConnectionState.Closed)
                _conn.Open();
        }
        public void CloseConnect()
        {
            if ((_conn != null) && (_conn.State == ConnectionState.Open))
                _conn.Close();
        }
        public DataTable dtbHang(string str)
        {
            DataTable dt = new DataTable();
            try
            {
                OpenConnect();
                da = new SqlDataAdapter(str, _conn);
                SqlCommand sqlcmd = new SqlCommand(str, _conn);
                sqlcmd.ExecuteNonQuery();
                da.Fill(dt);
                CloseConnect();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dt;
        }

        public bool InsertHang(HangEntity HangE)
        {
            string strInsert = "insert into TBL_Hang(IDHang,Tenhang,Motahang," +
                "Donvitinh,Soluongton,Nguong,Phanloai,Nguongoc,Size,Chatlieu,Loai) values (";
            strInsert += "N'" + HangE.IDHang + "',";
            strInsert += "N'" + HangE.TenHang + "',";
            strInsert += "N'" + HangE.MoTaHang + "',";
            strInsert += "N'" + HangE.DonViTinh + "',";
            strInsert += "'" + HangE.SoLuongTon + "',";
            strInsert += "'" + HangE.Nguong + "',";
            strInsert += "N'" + HangE.PhanLoai + "',";
            strInsert += "N'" + HangE.NguonGoc + "',";
            strInsert += "'" + HangE.Size + "',";
            strInsert += "N'" + HangE.ChatLieu + "',";
            strInsert += "N'" + HangE.Loai + "')";
            try
            {
                OpenConnect();
                SqlCommand cmd = new SqlCommand(strInsert, _conn);
                if (cmd.ExecuteNonQuery() <= 0)
                    return false;
                CloseConnect();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return true;
        }

        public bool UpdateHang(HangEntity HangE)
        {
            string strUpdate = "update  TBL_Hang set Tenhang = N'" + HangE.TenHang +
                "', Motahang = '" + HangE.MoTaHang + "', Donvitinh = '" + HangE.DonViTinh +
                "', Soluongton = '" + HangE.SoLuongTon + "', Nguong = '" + HangE.Nguong +
                "', Phanloai = '" + HangE.PhanLoai + "', Nguongoc = '" + HangE.NguonGoc +
                "', Size = '" + HangE.Size + "', ChatLieu = '" + HangE.ChatLieu +
                "',Loai = '" + HangE.Loai + "' where IDHang = '" + HangE.IDHang + "'";
            try
            {
                OpenConnect();
                SqlCommand sqlcmd = new SqlCommand(strUpdate, _conn);
                if (sqlcmd.ExecuteNonQuery() <= 0)
                    return false;
                CloseConnect();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return true;
        }

        public bool DeleteHang(string _strHang)
        {
            string _strsql = "delete from TBL_Hang where IDHang = '" + _strHang + "'";
            try
            {
                OpenConnect();
                SqlCommand sqlcmd = new SqlCommand(_strsql, _conn);
                if (sqlcmd.ExecuteNonQuery() <= 0)
                {
                    CloseConnect();
                    return false;
                }

            }
            catch (Exception ex)
            {
                CloseConnect();
                MessageBox.Show(ex.Message);
            }
            return true;
        }

        public DataTable GetTableHang(string strHang)
        {
            DataTable dt = new DataTable();
            string strget = "select * from TBL_Hang where IDHang = '" + strHang + "'";
            try
            {
                OpenConnect();
                da = new SqlDataAdapter(strget, _conn);
                SqlCommand sqlcmd = new SqlCommand(strget, _conn);
                sqlcmd.ExecuteNonQuery();
                da.Fill(dt);
                CloseConnect();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dt;
        }



    }
}
